package com.jsyl.dao.authority;

import com.jsyl.dao.base.BaseDao;
import com.jsyl.dao.base.EntityBuiler;
import com.jsyl.dao.base.SqlBuilder;
import com.jsyl.entity.authority.Child;
import com.jsyl.entity.authority.ChildAbilityFeedbackConfig;
import com.jsyl.entity.authority.SensoryIntegration;
import com.jsyl.infra.utils.page.PageModel;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.*;


/**
 *
 */
@Repository
public class SensoryIntegrationDao extends BaseDao<SensoryIntegration> {


    @Override
    public PageModel<SensoryIntegration> query(PageModel<SensoryIntegration> pm, SensoryIntegration sensoryIntegration) {
        StringBuilder sql = new StringBuilder();
        StringBuilder totalsql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("sub.project_code,si.id as ID,si.code as CODE,si.name as NAME,si.id as ID,c.name as CHILDNAME," +
                "(select value from  child_ability_feedback_config  sac where  name='dqnlnl' and child_id=:child_id and code = si.code) as dqnlnl," +
                "(select value from  child_ability_feedback_config  sac where  name='dqnlnj' and child_id=:child_id and code = si.code) as dqnlnj," +
                "(select value from  child_ability_feedback_config  sac where  name='dqnddj' and child_id=:child_id and code = si.code)as dqnddj," +
                "(select value from  child_ability_feedback_config  where  name='vqt' and child_id=:child_id and code = si.code)as vqt," +
                "(select value from  child_ability_feedback_config  where  name='ljtgcs' and child_id=:child_id and code = si.code) as ljtgcs")
                .append(SqlBuilder.SQL_FROM);
        totalsql.append(SqlBuilder.SQL_SELECT).append("count(si.id)").append(SqlBuilder.SQL_FROM);
        whereSql.append(
                "schedule_project_subject sub " +
                        "left join sensory_integration si on si.code=sub.project_code " +
                        "left join child c on c.id= sub.child_id")
                .append(SqlBuilder.SQL_WHERE).append("c.id = :child_id ");

        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("child_id", sensoryIntegration.getChildId());

        if (StringUtils.isNotEmpty(sensoryIntegration.getName())) {
            whereSql.append(SqlBuilder.SQL_AND).append("si.name").append(SqlBuilder.SQL_LIKE).append(":name");
            paramMap.put("name", "%" + sensoryIntegration.getName() + "%");
        }

        if (StringUtils.isNotEmpty(sensoryIntegration.getCode())) {
            whereSql.append(SqlBuilder.SQL_AND).append("si.code").append(SqlBuilder.SQL_LIKE).append(":code");
            paramMap.put("code", sensoryIntegration.getCode() + "%");
        }

        if (null!=(sensoryIntegration.getScheduleDetailId())) {
            whereSql.append(SqlBuilder.SQL_AND).append("sub.schedule_id").append("=").append(":schedule_id");
            paramMap.put("schedule_id", sensoryIntegration.getScheduleDetailId());
        }

        totalsql.append(whereSql);
        logger.info("totalsql : " + "\"" + totalsql.toString() + "\"");
        Integer totalCount = namedTemplate.queryForObject(totalsql.toString(), paramMap, Long.class).intValue();


        Integer startIndex = pm.getStartIndex();
        Integer pageSize = pm.getPageSize();

        if (pageSize == null || pageSize <= 0) {
            pageSize = PageModel.DEFAULT_PAGE_SIZE;
        }

        if (startIndex == null || startIndex < 0) {
            startIndex = 0;
        } else if (startIndex > totalCount) {
            Integer totalPage = (int) Math.ceil(totalCount.doubleValue() / pageSize.doubleValue());
            startIndex = (totalPage - 1) * pageSize;
        }

        whereSql.append(SqlBuilder.SQL_LIMIT);
        if (startIndex != null && startIndex >= 0) {
            whereSql.append(startIndex).append(",");
        }
        whereSql.append(pageSize);

        sql.append(whereSql);

        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        Map<String, String> columnMapper = new HashMap<>();
        List<SensoryIntegration> list = new ArrayList<SensoryIntegration>();
        SensoryIntegration vo = null;
        for (Iterator<Map<String, Object>> iterator = resultList.iterator(); iterator.hasNext(); ) {
            Map<String, Object> record = iterator.next();
            vo = new SensoryIntegration();
            String projects = "";
            String childName = "";
            Integer childId = null;
            String codename = "";
            String dqnlnl = "";
            String dqnlnj = "";
            String dqnddj = "";
            String vqt = "";
            String ljtgcs = "";
            String dqnlnlStr = "";
            String dqnlnjStr = "";
            String dqnddjStr = "";
            String vqtStr = "";
            String code = "";
            vo.setId((Integer) record.get("ID"));

            if (!StringUtils.isEmpty((String) record.get("NAME"))) {
                codename = (String) record.get("NAME");

            }

            if (!StringUtils.isEmpty((String) record.get("dqnlnl"))) {
                dqnlnl = (String) record.get("dqnlnl");
                switch (dqnlnl) {
                    case "1":
                        dqnlnlStr = "4-5岁";
                        break;
                    case "2":
                        dqnlnlStr = "6-7岁";
                        break;
                    case "3":
                        dqnlnlStr = "8-9岁";
                        break;
                    case "4":
                        dqnlnlStr = "10-11岁";
                        break;
                    case "5":
                        dqnlnlStr = "12-14岁";
                        break;
                    default:
                        dqnlnlStr = "14岁以上";
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) record.get("dqnlnj"))) {
                dqnlnj = (String) record.get("dqnlnj");
                switch (dqnlnj) {
                    case "-1":
                        dqnlnjStr = "无限制";
                        break;
                    case "0":
                        dqnlnjStr = "学前";
                        break;
                    default:
                        dqnlnjStr = dqnlnj + "年级";
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) record.get("dqnddj"))) {
                dqnddj = (String) record.get("dqnddj");
                switch (dqnddj) {
                    case "-1":
                        dqnddjStr = "无限制";
                        break;
                    case "1":
                        dqnddjStr = "低";
                        break;
                    case "2":
                        dqnddjStr = "中";
                        break;
                    case "3":
                        dqnddjStr = "高";
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) record.get("vqt"))) {
                vqt = (String) record.get("vqt");
                switch (vqt) {
                    case "0":
                        vqtStr = "低优先";
                        break;
                    case "1":
                        vqtStr = "高优先";
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) record.get("ljtgcs"))) {
                ljtgcs = (String) record.get("ljtgcs");
            }
            if (!StringUtils.isEmpty((String) record.get("CODE"))) {
                code = (String) record.get("CODE");
                projects = code.substring(0, 1);
                if (projects.equals("0")) {
                    projects = "视觉能力";
                } else {
                    projects = "听觉能力";
                }
            }

            if (!StringUtils.isEmpty((String) record.get("CHILDNAME"))) {
                childName = (String) record.get("CHILDNAME");
            }

            if (null != (Integer) record.get("CHILDID")) {
                childId = (Integer) record.get("CHILDID");
            }

            vo.setName(codename);
            vo.setDqnddj(dqnddj);
            vo.setDqnlnj(dqnlnj);
            vo.setDqnlnl(dqnlnl);
            vo.setVqt(vqt);
            vo.setDqnddjStr(dqnddjStr);
            vo.setDqnlnjStr(dqnlnjStr);
            vo.setDqnlnlStr(dqnlnlStr);
            vo.setVqtStr(vqtStr);
            vo.setLjtgcs(ljtgcs);
            vo.setCategory(projects);
            vo.setCode(code);
            vo.setChildName(childName);
            vo.setChildId(childId);
            list.add(vo);
        }
        pm.setTotalCount(totalCount);
        pm.setPageData(list);

        return pm;

    }


    //获得当前还在未选择的
    public List<SensoryIntegration> findVisual(String childId) {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append("distinct(project_code) as code").append(SqlBuilder.SQL_FROM);
        whereSql.append("schedule_project_subject")
                .append(SqlBuilder.SQL_WHERE).append(" child_id=:childId and project_code like '0%'");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("childId", childId);
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        String code = "";
        for (int i = 0; i < resultList.size(); i++) {
            code += (String) resultList.get(i).get("code") + ",";
        }
        if (code.length() > 0) {
            code += findVisualEnd(childId);
            code = code.substring(0, code.length() - 1);
            return findVisualCode(code);
        }else {
            return null;
        }
    }

    //获得当前还在未选择的
    private List<SensoryIntegration> findVisualCode(String codes) {
        StringBuilder sql = new StringBuilder();
        StringBuilder whereSql = new StringBuilder();
        sql.append(SqlBuilder.SQL_SELECT).append(" id AS ID,code AS CODE,parent_code AS PARENTCODE,name AS NAME ").append(SqlBuilder.SQL_FROM);
        whereSql.append("sensory_integration")
                .append(SqlBuilder.SQL_WHERE).append(" code like '0%' and code not in(").append(codes).append(")");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        sql.append(whereSql);
        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        List<SensoryIntegration> sensoryIntegrationList=new ArrayList<SensoryIntegration>();
        for (int i = 0; i < resultList.size(); i++) {
            SensoryIntegration sensoryIntegration =new SensoryIntegration();
            sensoryIntegration.setId((Integer) resultList.get(i).get("ID"));
            sensoryIntegration.setCode((String) resultList.get(i).get("CODE"));
            sensoryIntegration.setParentCode((String) resultList.get(i).get("PARENTCODE"));
            sensoryIntegration.setName((String) resultList.get(i).get("NAME"));
            sensoryIntegrationList.add(sensoryIntegration);
        }
        return sensoryIntegrationList;
    }

    //获得已经做完题目的
    private String findVisualEnd(String childId) {
        StringBuilder sql = new StringBuilder(" SELECT project_code as CODE FROM available_visual_projects where child_id=:childId and (training_times=trained_times)");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (childId.length()>0){
            paramMap.put("childId", childId);
        }
        logger.info("SQL : " + "\"" + sql.toString() + "\"");
        List<Map<String, Object>> resultList = namedTemplate.queryForList(sql.toString(), paramMap);
        String code = "";
        for(int i=0;i<resultList.size();i++){
            code +=(String)resultList.get(i).get("CODE")+",";
        }
        return code;
    }


}

